package com.yenlien.traceyun.control.dao;


import com.yenlien.traceyun.control.entity.ScienceInfo;
import com.yenlien.traceyun.webase.dao.BaseJpaDao;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

/**
 * 科技服务
 */
public interface ScienceInfoDao extends BaseJpaDao<ScienceInfo> {
    @Query(value = "select id,content,img_path,create_time,second_id,title_name from t_science_info where second_id=?1", nativeQuery = true)
    List<ScienceInfo> findBySecondId(String secondId);

    @Query(value = " select sf.id,sf.content,sf.img_path,sf.create_time," +
            "sf.second_id,sf.title_name,ls.name as secondName,ls.id as secondId,lt.id as topId," +
            "lt.name as topName from t_science_info sf" +
            " inner join t_level_second ls on ls.id=sf.second_id" +
            " inner join t_level_top lt on lt.id=ls.top_id where sf.id=?1", nativeQuery = true)
    List<Map<String, Object>> findById(String id);

    @Query(value = "select sf.id,sf.content,sf.img_path,sf.create_time,sf.second_id,sf.title_name," +
            "lt.id as topId,lt.name as topName from t_science_info sf" +
            " inner join t_level_top lt on lt.id=sf.second_id" +
            " where sf.id=?1", nativeQuery = true)
    List<Map<String, Object>> findByTopId(String id);


    @Query(value = " select sf.id,sf.content,sf.create_time,sf.img_path,sf.second_id,sf.title_name," +
            "ls.name as secondName,ls.top_id,lt.name as topName,(select count(sf.id) from t_science_info sf " +
            " inner join t_level_second ls on ls.id=sf.second_id" +
            " inner join t_level_top lt on lt.id=ls.top_id where ls.top_id=?1) as total from t_science_info sf " +
            " inner join t_level_second ls on ls.id=sf.second_id" +
            " inner join t_level_top lt on lt.id=ls.top_id" +
            " where ls.top_id=?1  limit ?2,?3", nativeQuery = true)
    List<Map<String, Object>> findByPageAble(String topId, int page, int size);


    @Query(value = " select sf.id,sf.content,sf.create_time,sf.img_path,sf.second_id,sf.title_name," +
            "ls.name as secondName,ls.top_id,lt.name as topName,(select count(sf.id) from t_science_info sf " +
            " inner join t_level_second ls on ls.id=sf.second_id" +
            " inner join t_level_top lt on lt.id=ls.top_id where ls.top_id=?1 and sf.second_id=?2) as total from t_science_info sf " +
            " inner join t_level_second ls on ls.id=sf.second_id" +
            " inner join t_level_top lt on lt.id=ls.top_id" +
            " where ls.top_id=?1 and sf.second_id=?2 limit ?3,?4", nativeQuery = true)
    List<Map<String, Object>> findByPageAble2(String topId, String secondId, int page, int size);
}
